package br.ufrj.neg.reuniao.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import br.ufrj.neg.bean.PapelBean;
import br.ufrj.neg.model.NegociacaoElementoModel;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.reuniao.bean.ReuniaoBean;
import br.ufrj.neg.reuniao.bean.TipoReuniaoBean;
import br.ufrj.neg.util.Data;

public class ReuniaoModel {
	
	/*
	---
	Esse metodo nao deve existir uma vez que na classe PapelModel existe um identico a ele.
	---
	public Collection<PapelBean> listaPapel() {
		ResultSet rs = null;
		PreparedStatement ps = null;

		Connection conn = ServiceLocator.getConnection();
		try {
			Collection<PapelBean> col = new ArrayList<PapelBean>();
			String sql = "Select * from reuniao";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				col.add(new PapelBean(rs.getString("PAPE_CD_ID"), rs
						.getString("PAPE_DS_PAPEL")));
			}
			return col;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	 */
	public int cadastraReuniao(ReuniaoBean reuniao) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "INSERT INTO reuniao(" + "tire_cd_id," + "nego_cd_id,"
					+ "reun_dt_data," + "reun_ds_local," + "reun_tx_descricao"
					+ ") values(?, ?, ?, ?, ?)";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, reuniao.getTipoReuniao());
			ps.setInt(2, reuniao.getIdNegociacao());
			ps.setTimestamp(3, new java.sql.Timestamp(reuniao.getData().getSQLData().getTime()));
			//java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
			/*try {
				ps.setString(3, reuniao.getData().getDataBr());
			} catch (Exception e) {
				ps.setString(3, "");
			}*/
			ps.setString(4, reuniao.getLocal());
			ps.setString(5, reuniao.getDescricao());
			ps.executeUpdate();

			sql = "SELECT max(reun_cd_id) as \"id\" FROM reuniao";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			conn.commit();

			int idReuniao = -1;
			while (rs.next()) {
				idReuniao = rs.getInt("id");
			}

			return idReuniao;
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean atualizaReuniao(ReuniaoBean reuniao) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "update reuniao set " + "tire_cd_id=?, "
					+ "nego_cd_id=?, " + "reun_dt_data=?, "
					+ "reun_ds_local= ?, " + "reun_tx_descricao=? "
					+ "where reun_cd_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, reuniao.getTipoReuniao());
			ps.setInt(2, reuniao.getIdNegociacao());
			ps.setTimestamp(3, new java.sql.Timestamp(reuniao.getData().getSQLData().getTime()));
			ps.setString(4, reuniao.getLocal());
			ps.setString(5, reuniao.getDescricao());
			ps.setInt(6, reuniao.getId());
			ps.executeUpdate();
			conn.commit();
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	
	
	public ReuniaoBean getReuniao(int idReuniao) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "select * from reuniao where reun_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			rs = ps.executeQuery();
			ReuniaoBean reuniao = null;
			while (rs.next()) {
				reuniao = new ReuniaoBean();
				reuniao.setData(new Data(rs.getTimestamp("reun_dt_data")));
				reuniao.setDescricao(rs.getString("reun_tx_descricao"));
				reuniao.setId(rs.getInt("reun_cd_id"));
				reuniao.setIdNegociacao(rs.getInt("nego_cd_id"));
				reuniao.setLocal(rs.getString("reun_ds_local"));
				reuniao.setTipoReuniao(rs.getInt("tire_cd_id"));
			}
			return reuniao;
		} catch (Exception e) {
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	/**
	 * @param idNegocio
	 * @param trb - precisa de um TipoReuniaoBean vazio, com um idioma setado.  
	 * @return
	 */
	public Collection<ReuniaoBean> listaReuniaoporNegociacao(int idNegocio, TipoReuniaoBean trb) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "select * from reuniao where NEGO_CD_ID = "
					+ idNegocio + " order by reun_cd_id";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			Collection<ReuniaoBean> reunioes = new ArrayList<ReuniaoBean>();
			ReuniaoBean reuniao;
			NegociacaoElementoModel nem = new NegociacaoElementoModel();
			TipoReuniaoModel trm = new TipoReuniaoModel();
			
			while (rs.next()) {
				reuniao = new ReuniaoBean();						
				reuniao.setData(new Data(rs.getTimestamp("reun_dt_data")));
				reuniao.setDescricao(rs.getString("reun_tx_descricao"));
				reuniao.setId(rs.getInt("reun_cd_id"));
				reuniao.setIdNegociacao(rs.getInt("NEGO_CD_ID"));
				reuniao.setLocal(rs.getString("reun_ds_local"));
				reuniao.setTipoReuniao(rs.getInt("tire_cd_id"));
				trb.setId(reuniao.getTipoReuniao());				
				reuniao.setTipoReuniaoDescricao((trm.buscaTipoReuniao(trb).getDescricao()));
				reunioes.add(reuniao);
			}
			return reunioes;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public boolean removeReuniao(int idReuniao) {	
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {				
			/* Remove a reuniao */
			String sql = "delete from reuniao where reun_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			System.out.println(sql);
			ps.executeUpdate();

			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}

	}
}
